edocs Home > BEA AquaLogic Data Services Platform 3.0 Documentation

How To Use Java to Make Excel Data Available to Data Services

This topic describes the steps needed to create a Java project that can consume Excel data.

Save the attachments to a temporary directory.

Step 1 - Create a new workspace

Create a new server in the workspace.

Step 2 - Create a Java project named excel_jcom

1. On first page of wizard select:

   Create separate source and output folder

2. On second page of wizard select WebLogic92's jcom.jar. Use the Libraries tab > External Libraries > Select:

   weblogic92\server\lib\jcom.jar

3. Click Finish.

Step 3 - Enable XMLBeans builder for excel_jcom

Right-click on the excel_jcom project > Properties > XmlBeans > Enable XmlBeans Builder

Step 4 - Create a new package 

Create a new package named:

com.bea.dsp.wrappers.excel

Step 5 - Import the package 

Import Excel_JCOM.java into the new package. (Ignore any import errors that may appear.)

To test this example, you can download a ZIP file containing excel.xsd, Excel_JCOM.java, and test.xml.

Step 6 - Run the com2java function

 
Run weblogic92\server\bin\com2java with the following directives:

-output location:

<workspace>/excel_jcom/src/com/bea/dsp/wrappers/excel/jcom

 - output to package:

-output to package:

com.bea.dsp.wrappers.excel.jcom

 - input your Excel executable such as Start menu > Program Files > Microsoft Office > OFFICE11 > excel.exe

Step 7 - Import the Excel schema file into the src directory

Step 8 - Build your project

Your project should build automatically, all errors should disappear.

Step 9 - Create an ALDSP dataspace project

Create an ALDSP dataspace project. You can use the menu option:

Properties > Targets > <your aldsp server>

Step 10 - Export the excel_jcom project 

1. Export your excel_jcom project to:

   <dataspace-project>/DSP-INF/lib/excel_jcom.jar

2. Select:
   • src
   • .xbean_src
   • .xbean_bin
3. Select the first option: Export generated class files and resources.

Ignore any warning messages.

Step 11 - Create a folder to contain the Excel data 

1. In the dataspace project, create a folder named physical.
2. In that folder, create a physical data service of type Java
3. Browse to excel_jcom.jar, com.bea.dsp.wrappers.excel.Excel_JCOM.
4. Ignore the warnings, select only getExcel() method.
5. Give Excel for the name of the data service.

Step 12 - Open Excel

1. Open Excel.ds in Test view.
2. Select the getExcel() function.
3. Pass the following arguments: 

   Argument	Setting
   arg1	c:/path-to/test.xls
   arg2	Customers

4. Execute the function.

Notes

• The Excel_JCOM getExcel java method reads the entire Excel document and creates XML in two forms:

  (row\[\]col\[\] and cell\[\]
  

  You probably only need one format, so comment out the one you don't need.

• When you are using getExcel() in an xquery, call it once and store the result in a variable rather than making multiple calls.
• Excel_JCOM uses a cached instance of Excel and leaves it running. Therefore you will need to manually kill the Excel processes. This may affect performance.